<?php

namespace fastphp;

use fastphp\util\Log;
use PDO;
use PDOException;

class Db
{
    public ?PDO $link = null;
    private string $sql = '';
    private string $fields = '';
    private string $tableName = '';
    private string $wheres = '';
    private int $whereType = 0; // 默认：0代表用AND连接，1代表用OR连接
    private string $aliasName = '';
    private string $joins = '';
    private int $joinType = 0; // 默认：0代表内连接，1代表左连接，2代表右连接
    private string $joinTabName = '';
    private string $joinAsName = '';
    private string $orders = '';
    private ?int $offset = null;
    private ?int $perPage = null;
    // 单例实例
    private static $instance = null;
    // 连接池
    private static $connections = [];
    // 当前连接标识符
    private $connectionKey = 'default';

    /**
     * 构造函数支持无参初始化
     * @param string|null $tableName 表名（可选）
     * @param Config|null $config 配置对象（可选）
     */
    public function __construct(string $tableName = null, Config $config = null)
    {
        // 获取配置对象
        $this->config = $config ?? $this->getConfigInstance();

        if ($tableName !== null) {
            $prefix = $this->config->get('database.prefix', '');
            $this->tableName = $prefix . $tableName;
        }

        // 使用连接池获取连接，而不是每次创建新连接
        $this->getConnectionFromPool();
    }

    /**
     * 从连接池获取数据库连接
     */
    private function getConnectionFromPool()
    {
        $key = $this->connectionKey;

        if (!isset(self::$connections[$key]) || !self::$connections[$key] instanceof PDO) {
            // 连接不存在，创建新连接
            $this->connect();
        } else {
            // 重用现有连接
            $this->link = self::$connections[$key];
        }
    }

    // 修改connect方法，将连接保存到连接池
    private function connect(): bool
    {
        try {
            // 从配置中获取数据库连接信息
            $type = $this->config->get('database.type', 'mysql');
            $host = $this->config->get('database.host', 'localhost');
            $dbname = $this->config->get('database.name', '');
            $port = $this->config->get('database.port', '3306');
            $user = $this->config->get('database.user', 'root');
            $password = $this->config->get('database.password', '');

            $dsn = "{$type}:host={$host};dbname={$dbname};port={$port}";

            // 在生产环境关闭数据库连接日志
            if (defined('APP_DEBUG') && APP_DEBUG) {
                Log::debug("正在连接数据库: {$dsn}", 'db');
            }

            $this->link = new PDO($dsn, $user, $password);
            $this->link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->link->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            $this->link->exec('SET NAMES utf8mb4');

            // 保存连接到连接池
            self::$connections[$this->connectionKey] = $this->link;

            if (defined('APP_DEBUG') && APP_DEBUG) {
                Log::info("数据库连接成功: {$dsn}", 'db');
            }
            return true;
        } catch (PDOException $e) {
            $errorMsg = "数据库连接失败: " . $e->getMessage();
            Log::error($errorMsg, 'db');
            // 记录完整异常信息
            Log::logException($e, 'db_exception');
            return false;
        }
    }

    /**
     * @var Config 配置对象
     */
    private $config;

    public static function getInstance()
    {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * 获取配置实例
     * @return Config
     */
    private function getConfigInstance(): Config
    {
        // 从全局容器获取配置实例
        if (isset($GLOBALS['core']) && $GLOBALS['core']->get('config')) {
            return $GLOBALS['core']->get('config');
        }

        // 如果没有全局容器，创建新实例
        return new Config();
    }

    /**
     * 指定表名
     * @param string $tableName 表名
     * @return $this
     */
    public function table(string $tableName): Db
    {
        $prefix = $this->config->get('database.prefix', '');
        $this->tableName = $prefix . $tableName;
        return $this;
    }

    /**
     * 执行Sql增删改语句
     * @param string $sql 执行的sql语句
     * @return int 返回受影响的行数
     * @throws PDOException 当SQL执行失败时抛出异常
     */
    protected function sql_exce(string $sql): int
    {
        try {
            $stmt = $this->link->prepare($sql);
            // 记录开始时间
            $startTime = microtime(true);
            $stmt->execute();
            // 记录结束时间
            $endTime = microtime(true);
            // 计算执行时间
            $executionTime = $endTime - $startTime;
            $affectedRows = $stmt->rowCount();

            // 记录详细的SQL执行日志
            $this->logSqlExecution($this->sql, $executionTime, 'WRITE', $affectedRows);
            return $affectedRows;
        } catch (PDOException $e) {
            $this->handleSqlException($e, $this->sql);
            // 抛出异常让上层处理，而不是直接die
            throw $e;
        }
    }

    /**
     * 执行Sql查询语句
     * @param string $sql 执行的sql语句
     * @param bool $fetchAll 是否获取所有结果集
     * @return array 返回结果集数组数据
     * @throws PDOException 当SQL执行失败时抛出异常
     */
    protected function sql_query(string $sql, bool $fetchAll = true): array
    {
        try {
            $stmt = $this->link->prepare($sql);
            // 记录开始时间
            $startTime = microtime(true);
            $stmt->execute();
            // 记录结束时间
            $endTime = microtime(true);
            // 计算执行时间
            $executionTime = $endTime - $startTime;

            $result = $fetchAll ? $stmt->fetchAll() : ($stmt->fetch() !== false ? $stmt->fetch() : []);
            $resultCount = count($result);

            // 记录详细的SQL执行日志
            $this->logSqlExecution($this->sql, $executionTime, 'READ', $resultCount);
            return $result;
        } catch (PDOException $e) {
            $this->handleSqlException($e, $this->sql);
            // 抛出异常让上层处理，而不是直接die
            throw $e;
        }
    }

    /**
     * 记录SQL执行日志
     * @param string $sql SQL语句
     * @param float $executionTime 执行时间（秒）
     * @param string $operationType 操作类型（READ/WRITE）
     * @param int $count 结果数量或受影响行数
     */
    private function logSqlExecution(string $sql, float $executionTime, string $operationType, int $count): void
    {
        // 在生产环境，只记录执行时间超过200ms的慢查询
        if ((defined('APP_DEBUG') && APP_DEBUG) || $executionTime > 0.2) {
            $formattedTime = round($executionTime * 1000, 2) . 'ms';
            Log::warning("[慢查询] {$formattedTime} - {$sql}", 'db_slow');
        }
    }

    /**
     * 处理SQL异常
     * @param PDOException $e 异常对象
     * @param string $sql 执行的SQL语句
     */
    private function handleSqlException(PDOException $e, string $sql): void
    {
        // 记录错误日志
        Log::error("SQL错误: {$e->getMessage()}", 'db_error');

        // 记录完整的SQL语句（敏感信息已通过预处理参数化）
        Log::error("执行的SQL: {$sql}", 'db_error');

        // 记录异常详情
        Log::logException($e, 'db_exception');
    }

    /**
     * 查看生成的Sql语句
     * @return string 请放在链式调用的最后
     */
    public function getSql(): string
    {
        $this->spliceSql();
        return $this->sql;
    }

    /**
     * 查询多个数据
     * @return array 返回查询的数据
     */
    public function select(): array
    {
        $this->spliceSql();
        return $this->sql_query($this->sql);
    }

    /**
     * 拼接sql语句
     */
    private function spliceSql(): void
    {
        if (empty($this->sql)) {
            $this->sql = "SELECT ";
        }
        if (!empty($this->fields)) {
            $this->sql .= $this->fields . " FROM ";
        } else {
            $this->sql .= "* FROM ";
        }

        if (!empty($this->tableName)) {
            $this->sql .= "`$this->tableName`";
        }

        if (!empty($this->aliasName)) {
            $this->sql .= " AS " . $this->aliasName;
        }

        if (!empty($this->joinTabName)) {
            if ($this->joinType == 0) {
                $this->sql .= " INNER JOIN `$this->joinTabName`";
            } elseif ($this->joinType == 1) {
                $this->sql .= " LEFT JOIN `$this->joinTabName`";
            } elseif ($this->joinType == 2) {
                $this->sql .= " RIGHT JOIN `$this->joinTabName`";
            }
        }

        if (!empty($this->joinAsName)) {
            $this->sql .= " AS " . $this->joinAsName;
        }

        if (!empty($this->joins)) {
            $this->sql .= " ON " . $this->joins;
        }

        if (!empty($this->wheres)) {
            $this->sql .= " WHERE " . $this->wheres;
        }

        if (!empty($this->orders)) {
            $this->sql .= " ORDER BY " . $this->orders;
        }
        if ($this->offset !== null && $this->perPage !== null) {
            $this->sql .= " LIMIT $this->offset, $this->perPage";
        }
    }

    /**
     * 查询单个数据
     * @return array 返回查询的数据
     */
    public function find(): array
    {
        $this->spliceSql();
        return $this->sql_query($this->sql, false);
    }

    /**
     * 修改方法 - 使用预处理语句防止SQL注入
     * @param array $data 需要的修改参数
     * @return int 返回受影响的行数
     * @throws PDOException 当更新失败时抛出异常
     */
    public function update(array $data): int
    {
        if (empty($data)) {
            Log::info("更新操作：未提供更新数据", 'db');
            return 0;
        }

        // 记录操作意图
        Log::debug("准备执行更新操作，表: {$this->tableName}, 字段数: " . count($data), 'db');

        // 使用预处理语句
        $this->sql = "UPDATE `$this->tableName` SET ";
        $setClause = [];
        $params = [];

        foreach ($data as $key => $value) {
            $setClause[] = "`$key` = ?";
            $params[] = $value;
        }

        $this->sql .= implode(', ', $setClause);

        if (!empty($this->wheres)) {
            $this->sql .= " WHERE " . $this->wheres;
        } else {
            // 记录警告日志
            Log::warning("更新操作没有WHERE条件", 'db');
        }

        try {
            $stmt = $this->link->prepare($this->sql);
            $startTime = microtime(true);
            $stmt->execute($params);
            $endTime = microtime(true);
            $executionTime = $endTime - $startTime;
            $affectedRows = $stmt->rowCount();

            // 记录执行结果
            $this->logSqlExecution($this->sql, $executionTime, 'UPDATE', $affectedRows);
            return $affectedRows;
        } catch (PDOException $e) {
            $this->handleSqlException($e, $this->sql);
            throw $e;
        }
    }

    /**
     * 删除方法
     * @return int 返回受影响的行数
     * @throws PDOException 当删除失败时抛出异常
     */
    public function delete(): int
    {
        $this->sql = "DELETE FROM `" . $this->tableName . "`";

        if (!empty($this->wheres)) {
            $this->sql .= " WHERE " . $this->wheres;
        } else {
            // 防止误删全表
            $errorMsg = 'Deleting without WHERE condition is not allowed';
            Log::critical($errorMsg, 'db_security');
            trigger_error($errorMsg, E_USER_ERROR);
            return 0;
        }

        // 记录操作意图
        Log::debug("准备执行删除操作，表: {$this->tableName}", 'db');
        return $this->sql_exce($this->sql);
    }

    /**
     * 插入方法 - 使用预处理语句防止SQL注入
     * @param array $data 需要的插入参数
     * @return int 返回受影响的行数
     * @throws PDOException 当插入失败时抛出异常
     */
    public function insert(array $data): int
    {
        if (empty($data)) {
            Log::info("插入操作：未提供插入数据", 'db');
            return 0;
        }

        // 记录操作意图
        $isBatch = isset($data[0]) && is_array($data[0]);
        $recordCount = $isBatch ? count($data) : 1;
        Log::debug("准备执行插入操作，表: {$this->tableName}, 记录数: {$recordCount}", 'db');

        $this->sql = "INSERT INTO `" . $this->tableName . "` ";

        // 批量插入处理
        if ($isBatch) {
            $keys = array_keys($data[0]);
            $placeholders = [];
            $params = [];

            // 构建插入字段
            $this->sql .= "(`" . implode('`, `', $keys) . "`) VALUES ";

            // 构建占位符和参数
            foreach ($data as $row) {
                $rowPlaceholders = array_fill(0, count($keys), '?');
                $placeholders[] = "(" . implode(', ', $rowPlaceholders) . ")";
                $params = array_merge($params, array_values($row));
            }

            $this->sql .= implode(', ', $placeholders);
        } else {
            // 单条插入
            $keys = array_keys($data);
            $values = array_values($data);
            $placeholders = array_fill(0, count($keys), '?');

            $this->sql .= "(`" . implode('`, `', $keys) . "`) VALUES (" . implode(', ', $placeholders) . ")";
            $params = $values;
        }

        try {
            $stmt = $this->link->prepare($this->sql);
            $startTime = microtime(true);
            $stmt->execute($params);
            $endTime = microtime(true);
            $executionTime = $endTime - $startTime;
            $affectedRows = $stmt->rowCount();

            // 记录执行结果
            $this->logSqlExecution($this->sql, $executionTime, 'INSERT', $affectedRows);
            return $affectedRows;
        } catch (PDOException $e) {
            $this->handleSqlException($e, $this->sql);
            throw $e;
        }
    }

    /**
     * 条件方法
     * @param array|string $firstExp 字符串或数组表达式
     * @param string|null $symbol 符号表达式
     * @param string|null $lastExp 字符串或数组表达式
     * @return $this 返回当前对象
     */
    public function where($firstExp, string $symbol = null, string $lastExp = null): Db
    {
        $exp = $this->whereType ? ' OR ' : ' AND ';

        if (is_array($firstExp)) {
            foreach ($firstExp as $i => $condition) {
                if (!is_array($condition) || count($condition) < 3) {
                    continue;
                }

                $field = $condition[0];
                $op = $condition[1];
                $value = $condition[2];

                // 转义字段名
                $field = "`$field`";

                // 处理值
                $valueStr = is_string($value) ? "'" . addslashes($value) . "'" : $value;

                if (!empty($this->wheres)) {
                    $this->wheres .= $exp . $field . $op . $valueStr;
                } else {
                    $this->wheres .= $field . $op . $valueStr;
                }
            }
        } else {
            // 转义字段名
            $firstExp = "`$firstExp`";

            if (empty($lastExp)) {
                // 简化写法: where('field', value)
                $valueStr = is_string($symbol) ? "'" . addslashes($symbol) . "'" : $symbol;

                if (!empty($this->wheres)) {
                    $this->wheres .= $exp . $firstExp . ' = ' . $valueStr;
                } else {
                    $this->wheres .= $firstExp . ' = ' . $valueStr;
                }
            } else {
                // 完整写法: where('field', 'op', value)
                $valueStr = is_string($lastExp) ? "'" . addslashes($lastExp) . "'" : $lastExp;

                if (!empty($this->wheres)) {
                    $this->wheres .= " $exp $firstExp $symbol $valueStr";
                } else {
                    $this->wheres .= "$firstExp $symbol $valueStr";
                }
            }
        }

        return $this;
    }

    /**
     * 条件方法（OR连接）
     * @param array|string $firstExp 字符串或数组表达式
     * @param string|null $symbol 符号表达式
     * @param string|null $lastExp 字符串或数组表达式
     * @return $this 返回当前对象
     */
    public function whereOr($firstExp, ?string $symbol = null, ?string $lastExp = null): Db
    {
        $this->whereType = 1;
        $result = $this->where($firstExp, $symbol, $lastExp);
        $this->whereType = 0; // 重置回默认的AND连接
        return $result;
    }

    /**
     * 查询指定字段
     * @param string $data 需要查询的字段
     * @return $this 返回当前对象
     */
    public function field(string $data): Db
    {
        if (is_string($data)) {
            // 简单的字段名转义处理
            $fields = explode(',', $data);
            $escapedFields = [];
            foreach ($fields as $field) {
                $field = trim($field);
                // 处理别名情况
                if (strpos($field, ' as ') !== false || strpos($field, ' AS ') !== false) {
                    $parts = preg_split('/\s+as\s+/i', $field);
                    if (count($parts) == 2) {
                        $escapedFields[] = "`" . trim($parts[0]) . "` AS " . trim($parts[1]);
                    }
                } else {
                    $escapedFields[] = "`" . $field . "`";
                }
            }
            $this->fields = implode(', ', $escapedFields);
        }
        return $this;
    }

    /**
     * 设置表别名
     * @param string $name 表别名
     * @return $this 返回当前对象
     */
    public function alias(string $name): Db
    {
        if (is_string($name)) {
            $this->aliasName = $name;
        }
        return $this;
    }

    /**
     * 表连接
     * @param string $name 需要连接的表名
     * @param string|array $exp 连接条件
     * @return $this 返回当前对象
     */
    public function join(string $name, $exp): Db
    {
        $n = explode(" ", $name);
        $str = "";
        if (is_array($exp)) {
            $conditions = [];
            foreach ($exp as $condition) {
                if (is_array($condition) && count($condition) >= 3) {
                    $conditions[] = "`{$condition[0]}` {$condition[1]} `{$condition[2]}`";
                }
            }
            $str = implode(" AND ", $conditions);
        } else {
            $str .= $exp;
        }
        $this->joins = $str;
        $this->joinTabName = $n[0];
        $this->joinAsName = isset($n[1]) ? $n[1] : '';
        return $this;
    }

    /**
     * 左连接
     * @param string $name 需要连接的表名
     * @param string|array $exp 连接条件
     * @return $this 返回当前对象
     */
    public function leftJoin(string $name, $exp): Db
    {
        $this->joinType = 1;
        return $this->join($name, $exp);
    }

    /**
     * 右连接
     * @param string $name 需要连接的表名
     * @param string|array $exp 连接条件
     * @return $this 返回当前对象
     */
    public function rightJoin(string $name, $exp): Db
    {
        $this->joinType = 2;
        return $this->join($name, $exp);
    }

    /**
     * 排序
     * @param string $data 需要排序的字段
     * @return $this 返回当前实例对象
     */
    public function order(string $data): Db
    {
        if (is_string($data)) {
            $this->orders = $data;
        }
        return $this;
    }

    /**
     * 分页
     * @param integer $start 起始索引
     * @param integer $end 每页条数
     * @return $this 返回对象本身
     */
    public function limit(int $start, int $end): Db
    {
        $this->offset = $start;
        $this->perPage = $end;
        return $this;
    }

    /**
     * 自动计算分页
     * @param integer $page 页码
     * @param integer $perPage 每页的个数
     * @return $this 返回对象本身
     */
    public function page(int $page = 1, int $perPage = 10): Db
    {
        // 计算偏移量
        $offset = ($page - 1) * $perPage;
        $this->offset = $offset;
        $this->perPage = $perPage;
        return $this;
    }

    /**
     * 析构函数，关闭数据库连接
     */
    public function __destruct()
    {
        // 关闭数据库连接
        if ($this->link instanceof PDO) {
            // 显式设置为null以释放连接资源
            $this->link = null;
        }
    }
}
